Query Dependencies, Error Handling, and Query Folding Fundamentals in Power BI

Course: Microsoft Power BI Training

Topic: Query Dependencies, Error Handling, and Query Folding Fundamentals

Course Link: Microsoft Power BI Training – JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction

Power BI is a business intelligence platform used to connect data sources, transform data, create data models, and build interactive reports. Power Query is an important component of Power BI that helps users clean, combine, reshape, and prepare data before loading it into the data model.

When working with multiple data sources and complex transformations, three important concepts help make data preparation reliable and efficient:

• Query Dependencies: Understanding how queries depend on one another and the order in which they are logically evaluated.
• Error Handling: Identifying, managing, and correcting errors that occur during data import and transformation.
• Query Folding: Understanding how Power Query can delegate supported transformations to the underlying data source instead of processing every operation locally.

These concepts are particularly useful when working with large datasets, multiple queries, database connections, scheduled refreshes, and production Power BI reports.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the role of Power Query in the Power BI data preparation process.
• Identify query dependencies using the Query Dependencies view.
• Create and manage reference and duplicate queries.
• Understand how errors occur during data transformations.
• Use Keep Errors, Remove Errors, Replace Errors, and try expressions appropriately.
• Understand the purpose and benefits of query folding.
• Identify transformations that may prevent query folding.
• Use Power Query diagnostics and other techniques to investigate performance issues.
• Apply best practices to improve data quality, refresh reliability, and query performance.

3. Understanding Power Query Fundamentals

3.1 What Is Power Query?

Power Query is a data connectivity and transformation engine available in Power BI and other Microsoft products. It allows users to connect to data sources and perform operations before the data is loaded into a destination such as a Power BI semantic model.

Common data sources include:

• Microsoft Excel workbooks.
• CSV and text files.
• SQL Server and other supported databases.
• SharePoint and web-based sources.
• Cloud services and enterprise data platforms.

3.2 What Is a Query?

A query is a named set of instructions that retrieves data from a source and optionally transforms it. In Power Query Editor, every query can contain several Applied Steps describing how the data is processed.

For example, a sales query might perform the following operations:

1. Connect to a sales database.
2. Select the required sales table.
3. Remove unnecessary columns.
4. Filter records to include the required period.
5. Change data types.
6. Remove invalid records.
7. Load the cleaned data into the Power BI model.

3.3 What Is an Applied Step?

An Applied Step represents an individual transformation or operation in a query. Power Query records these steps so that the transformation process can be inspected and modified.

Examples include Source, Navigation, Filtered Rows, Removed Columns, Changed Type, Merged Queries, and Added Custom Column.

Applied Steps are typically evaluated according to their dependencies. Some steps can be reordered safely, while others depend on earlier operations and must preserve their logical sequence.

4. Query Dependencies in Power BI

4.1 Definition of Query Dependencies

Query dependencies describe the relationships between queries. A query depends on another query when it uses that query's output or references its logic as part of its own transformation process.

For example, an organization might have a source query named RawSales. A second query named CleanSales references it and removes invalid records. A third query named MonthlySales uses the cleaned data to calculate monthly totals.

The dependency structure can be represented as follows:

RawSales
   |
   v
CleanSales
   |
   v
MonthlySales

Here, CleanSales depends on RawSales, and MonthlySales depends on CleanSales.

4.2 Why Query Dependencies Matter

• Understand data lineage: Identify where a query's data originates.
• Reduce duplication: Reuse existing transformation logic through references.
• Improve maintainability: Make complex data preparation workflows easier to understand.
• Support troubleshooting: Trace incorrect results back to upstream queries.
• Organize data preparation: Separate source extraction, cleaning, and reporting logic.
• Identify shared dependencies: Understand which downstream queries use the same upstream query.

4.3 Viewing Query Dependencies

Power Query Editor provides a Query Dependencies view that displays the relationships between queries in a visual diagram.

To access it in Power BI Desktop:

1. Open Power BI Desktop.
2. Select Home > Transform data to open Power Query Editor.
3. Locate the View tab.
4. Select Query Dependencies.
5. Inspect the displayed query nodes and their connecting lines.
6. Select relevant queries to understand their upstream and downstream relationships.

The exact appearance and available commands can vary by Power BI version.

4.4 Understanding Dependency Diagrams

A dependency diagram typically displays queries as nodes connected by directional relationships. The connections help explain which queries provide data to other queries.

Consider the following example:

Excel Sales File
       |
       v
   RawSales
       |
       v
   CleanSales
       |
       +----------------+
       |                |
       v                v
 MonthlySales       RegionalSales

In this example, CleanSales supplies data to both MonthlySales and RegionalSales. Both downstream queries reuse the same cleaned dataset.

A dependency diagram helps explain logical relationships, but it should not be treated as a complete execution timeline. Actual evaluation, caching, and source requests depend on the query expressions, connector, and refresh environment.

4.5 Source Queries, Staging Queries, and Output Queries

This structure is a recommended organizational approach rather than a mandatory Power BI requirement. Not every project needs all these query types.

5. Referencing and Duplicating Queries

5.1 What Is a Reference Query?

A reference query creates a new query based on the output or expression of an existing query. It is useful when multiple downstream queries should reuse common transformation logic.

For example, a cleaned sales dataset can serve as the foundation for monthly sales, regional sales, and product sales reports.

To create a reference query:

1. Open Power Query Editor.
2. Right-click the query you want to reuse.
3. Select Reference.
4. Rename the new query.
5. Apply the additional transformations needed for the new purpose.

Example dependency structure:

StgSales
   |
   +------------------+
   |                  |
   v                  v
MonthlySales      ProductSales

5.2 What Is a Duplicate Query?

A duplicate query creates a separate copy of the selected query's current definition and transformation steps. The duplicate can then be changed independently.

To create one, right-click a query and select Duplicate.

Neither approach guarantees that the source data will be fetched only once. Power Query evaluation, caching, connector behavior, and refresh operations affect actual source requests.

5.3 Example of a Reference-Based Workflow

Suppose an Excel table contains sales records with the following columns:

OrderID | OrderDate | Region | Product | SalesAmount

Create a staging query called StgSales and use it to standardize data types and remove unnecessary columns. Then create two reference queries:

• RegionalSales filters and groups sales by region.
• ProductSales groups sales by product.

Both queries reuse the staging logic. This makes the workflow easier to maintain because a shared transformation can be updated in one place.

6. Error Handling in Power Query

6.1 What Is an Error?

An error occurs when Power Query cannot successfully evaluate a particular operation or expression. Errors may arise while retrieving source data, converting values, applying formulas, expanding nested data, or combining tables.

For example, converting the text value "ABC" into a whole number may produce an error because the value is not a valid integer.

6.2 Common Types of Errors

6.3 Identifying Errors in Power Query Editor

To investigate a query error:

1. Open Power Query Editor.
2. Select the affected query.
3. Review the Applied Steps pane to identify the step where the issue appears.
4. Inspect cells marked with an error indicator.
5. Select an error cell to view the available error details.
6. Check the error message, affected column, input value, and transformation logic.
7. Correct the cause or apply an appropriate error-handling strategy.

Do not immediately remove all error rows. First determine whether the errors indicate invalid data, a broken transformation, or a problem with the source.

6.4 Keep Errors

The Keep Errors command filters the selected data to retain rows containing errors. It is useful when investigating data quality problems because it isolates problematic records for inspection.

Example: A sales table has thousands of rows, but some values in the SalesAmount column cannot be converted to numbers. Keeping errors helps identify the affected records and determine whether the source values need correction.

6.5 Remove Errors

The Remove Errors command removes rows containing errors in the selected column or relevant selection. It can be appropriate when those records are genuinely invalid and excluding them is consistent with the business rules.

Use this option carefully. Removing error rows can reduce the number of records and affect financial totals, transaction counts, and reporting accuracy.

6.6 Replace Errors

The Replace Errors command replaces error values with a specified replacement value. It is useful when a business rule defines a valid fallback.

For example, if a missing or invalid optional quantity should be treated as zero, replacing the error with zero may be appropriate after validating that this interpretation is correct.

However, replacing an invalid sales amount with zero could hide a serious data quality issue. Use replacement values only when their meaning is justified.

6.7 Using try Expressions in Power Query M

The M language supports the try expression, which allows an operation to be evaluated while capturing information about an error rather than immediately allowing it to interrupt the expression.

Example:

let
    Result = try Number.From("123")
in
    Result

The result is a record containing fields such as HasError and either Value or Error.

To return a fallback value when conversion fails:

let
    Source = Table.FromRecords({
        [Amount = "150"],
        [Amount = "ABC"],
        [Amount = "250"]
    }),
    AddedAmount = Table.AddColumn(
        Source,
        "ConvertedAmount",
        each try Number.From([Amount]) otherwise null,
        type nullable number
    )
in
    AddedAmount

In this example, valid numeric text becomes a number, while the invalid value produces null in the new column. The original Amount column remains available for investigation.

This approach is useful when a dataset contains mixed or inconsistent values. In production, consider recording invalid values separately so that data quality problems remain visible.

6.8 Handling Date Conversion Errors

Date conversion can fail when incoming values use inconsistent formats, such as day-month-year and month-day-year.

Example:

let
    Source = Table.FromRecords({
        [OrderDateText = "21/10/2026"],
        [OrderDateText = "InvalidDate"]
    }),
    ConvertedDates = Table.AddColumn(
        Source,
        "OrderDate",
        each try Date.FromText(
            [OrderDateText],
            [Culture = "en-GB"]
        ) otherwise null,
        type nullable date
    )
in
    ConvertedDates

The specified culture helps Power Query interpret dates using the intended regional convention. Invalid values return null in this example rather than terminating the row calculation.

6.9 Creating an Error Flag

Sometimes it is better to preserve every row and add a column that identifies invalid values.

let
    Source = Table.FromRecords({
        [Amount = "100"],
        [Amount = "ABC"],
        [Amount = "300"]
    }),
    AddedCheck = Table.AddColumn(
        Source,
        "AmountStatus",
        each
            if (try Number.From([Amount])[HasError])
            then "Invalid"
            else "Valid",
        type text
    )
in
    AddedCheck

This example labels each source value as valid or invalid without deleting the original records. A separate conversion step can then create a numeric column for reporting.

6.10 Best Practices for Error Handling

• Identify the root cause before removing or replacing errors.
• Validate source data before applying strict data type conversions.
• Use appropriate culture settings for dates and numbers.
• Preserve original values when they are useful for auditing.
• Use nullable types where missing values are valid.
• Maintain an error-reporting query when data quality monitoring is important.
• Apply business-approved fallback rules instead of arbitrary replacements.
• Test error-handling logic using valid, invalid, blank, and unexpected input values.

7. Introduction to Query Folding

7.1 Definition of Query Folding

Query folding is the process in which Power Query translates supported transformation steps into a query or operation that the underlying data source can execute.

For example, when connecting to a SQL database, a filter applied in Power Query may be translated into a SQL WHERE clause. Instead of transferring every record to Power BI and filtering locally, the database can filter the records before returning them.

Query folding is particularly useful for relational databases and other connectors that support translating the relevant transformations.

7.2 Example of Query Folding

Imagine a database contains ten million sales records, but the report requires only records from 2026.

Without folding, the transformation might require a large amount of data to be retrieved before filtering locally.

With folding, the source may execute an operation conceptually similar to:

SELECT
    OrderID,
    OrderDate,
    Region,
    SalesAmount
FROM Sales
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2027-01-01';

The database returns only the matching records and selected columns. This SQL illustrates the general idea; Power Query does not necessarily generate this exact statement for every connector or transformation.

7.3 Benefits of Query Folding

• Improved performance: Data filtering and transformation can be executed efficiently by the source system.
• Reduced data transfer: Fewer unnecessary rows and columns may need to be sent to Power BI.
• Lower local processing requirements: Some work is handled by the source rather than the Power Query engine.
• Scalability: Large relational datasets can often be processed more efficiently.
• Faster refreshes: Folding may reduce refresh duration when supported operations are delegated successfully.

Performance improvements depend on source capacity, indexes, network conditions, query design, and the transformations involved. Query folding does not automatically guarantee a faster refresh in every situation.

7.4 Query Folding, Partial Folding, and Local Processing

These terms describe general execution patterns. The exact behavior depends on the connector, query expression, and data source.

8. How to Check Query Folding

8.1 View Native Query

For supported connectors, Power Query Editor may provide a View Native Query option for a selected step.

To investigate:

1. Open Power Query Editor.
2. Select a query that connects to a folding-capable source, such as SQL Server.
3. Select a step such as Filtered Rows.
4. Right-click the step or use the relevant context menu.
5. Check whether View Native Query is available.
6. If available, inspect the generated source query to understand which transformations have been translated.

If the option is unavailable, this does not conclusively prove that no folding occurs. The connector may not expose the option, the source may use a different mechanism, or the selected step may not support it.

8.2 Folding Indicators

Some Power Query experiences and connectors provide indicators that describe folding behavior. When available, these can help identify whether a step is folded, partially folded, or evaluated locally.

Availability depends on the Power Query environment and connector. Do not assume every Power BI installation displays the same indicators.

8.3 Query Diagnostics

Query Diagnostics helps investigate how Power Query evaluates queries and interacts with data sources. It can provide information about evaluations, operations, and data source activity.

Use diagnostics when:

• A query refresh takes longer than expected.
• A source appears to be queried repeatedly.
• A transformation unexpectedly increases processing time.
• You need to distinguish source activity from local processing.

Diagnostics can generate detailed output. Test on representative data and avoid drawing conclusions from a single timing measurement.

9. Transformations That Commonly Affect Query Folding

9.1 Filtering Rows

Filtering rows is often foldable for relational sources when the filter expression is supported by the connector.

Example:

Table.SelectRows(
    Source,
    each [Region] = "West"
)

This can be translated into a source-side filter. However, folding depends on the data source and the expression.

9.2 Selecting Columns

Removing unnecessary columns early can reduce the amount of data transferred and may translate into a source-side projection.

Table.SelectColumns(
    Source,
    {"OrderID", "Region", "SalesAmount"}
)

This expression keeps only the specified columns. If the connector supports folding, Power Query may request only those fields from the source.

9.3 Changing Data Types

Simple type conversions are often foldable when the source supports equivalent operations. More complex conversions, locale-dependent parsing, or unsupported functions may require local evaluation.

9.4 Adding Custom Columns

Custom columns containing simple supported arithmetic or conditional expressions may fold. Expressions using unsupported M functions, specialized text operations, or complex row-level logic may not fold.

9.5 Merging Queries

Merging queries can fold when the participating sources and join operations support folding and the queries remain compatible. Merging queries from unrelated sources may prevent the operation from being fully delegated.

9.6 Grouping Rows

Grouping and aggregation operations may fold when the source supports the corresponding operations. The aggregation functions, data types, and connector capabilities determine whether the operation is translated.

9.7 Common Causes of Folding Breaks

• Using an M function that the connector cannot translate.
• Combining queries from different sources or incompatible source contexts.
• Introducing a non-foldable transformation before later filtering steps.
• Using custom logic that must be evaluated by the Power Query engine.
• Invoking functions that require local processing or materialized values.
• Changing the query structure in a way that prevents source-side optimization.

These operations do not always break folding. The actual outcome depends on the connector, source, expression, and position of each step in the query.

10. Best Practices for Improving Query Folding

1. Filter early: Apply selective filters near the beginning of the query when practical and when doing so preserves the intended results.
2. Remove unnecessary columns early: Avoid transferring data that the report does not need.
3. Prefer supported transformations: Use standard filtering, projection, joins, and aggregations when they meet the business requirement.
4. Check folding after important changes: Verify whether source-side execution still occurs after modifying a query.
5. Use source-side views when appropriate: Database views can centralize reusable logic, security rules, and complex transformations.
6. Avoid unnecessary custom functions: Complex row-by-row functions may prevent source-side execution.
7. Maintain compatible sources: Queries from the same source system may offer more folding opportunities than combinations of unrelated sources.
8. Measure performance: Use diagnostics and refresh timings rather than assuming that a folded query is always optimal.
9. Protect credentials and privacy: Review data source permissions and privacy settings when combining data sources.

11. Understanding Query Dependencies and Query Folding Together

Query dependencies describe the relationships between queries, while query folding describes where supported transformations are executed. These concepts are related but are not the same.

Consider a sales reporting workflow:

SQL Sales Table
       |
       v
   StgSales
   - Select columns
   - Filter dates
   - Set data types
       |
       +---------------------+
       |                     |
       v                     v
 RegionalSales           ProductSales
 - Group by region       - Group by product
 - Handle invalid data   - Validate quantities
       |                     |
       v                     v
 Regional Report         Product Report

The dependency structure shows how both reporting queries use the staging query. Query folding may allow some filters, column selections, and aggregations to execute in SQL. Error handling determines how invalid values are managed in each transformation.

12. Practical Project: Build a Reliable Sales Query Workflow

12.1 Project Objective

Create a Power BI data preparation workflow that imports sales data, reuses transformation logic, identifies invalid values, and investigates query folding where the source supports it.

12.2 Sample Data

12.3 Step 1: Import the Data

1. Open Power BI Desktop.
2. Import the sales dataset from Excel, CSV, or a supported database.
3. Select Transform Data.
4. Rename the initial query to StgSales.

12.4 Step 2: Apply Basic Transformations

• Remove unnecessary columns.
• Standardize column names.
• Trim unnecessary spaces from text columns when needed.
• Validate that OrderID contains valid identifiers.
• Convert SalesAmount into a numeric value using an appropriate error-handling strategy.

12.5 Step 3: Create a Safe Numeric Column

In the staging query, add a custom column using the following M expression:

try Number.From([SalesAmount]) otherwise null

Name the column SalesAmountNumeric and set its type to a nullable numeric type where appropriate. Keep the original value if it is needed for auditing.

12.6 Step 4: Create a Data Quality Check

Add a status column to distinguish valid and invalid values:

if (try Number.From([SalesAmount])[HasError])
then "Invalid"
else "Valid"

Use this status to investigate invalid rows before excluding them from the final reporting dataset.

12.7 Step 5: Create Reference Queries

1. Right-click StgSales.
2. Select Reference.
3. Rename the first reference to RegionalSales.
4. Group the data by Region and calculate the sum of valid numeric sales amounts.
5. Create another reference called ProductSales.
6. Group the data by Product and calculate the sum of valid numeric sales amounts.

12.8 Step 6: Investigate Query Folding

If the data comes from a folding-capable database:

1. Apply a filter on the source date or region columns.
2. Inspect the available native-query or folding indicators.
3. Check whether column selection and filtering are translated to the source.
4. Review whether subsequent transformations continue to fold.
5. Compare refresh performance before and after meaningful changes.

If the source is a local CSV or Excel file, SQL-style query folding may not be available. Use the exercise to understand dependencies and error handling, and use a suitable database source to practice folding.

12.9 Step 7: Validate the Results

• Confirm that invalid sales values are clearly identified.
• Verify that valid records contribute correctly to regional and product totals.
• Check that reference queries update when upstream logic changes.
• Inspect query dependencies to confirm the intended relationships.
• Refresh the model and verify that the queries complete successfully.

13. Common Problems and Troubleshooting

14. Performance and Maintenance Best Practices

• Keep source, staging, transformation, and output queries clearly named.
• Use reference queries to reuse shared transformations where appropriate.
• Disable load for helper queries that do not need to be loaded into the model, while keeping required downstream dependencies intact.
• Filter and select columns early when this supports efficient processing.
• Investigate folding behavior after introducing custom functions or complex merges.
• Use error flags or separate error-reporting queries to monitor data quality.
• Avoid replacing all errors with zero or removing all error rows without understanding their meaning.
• Document unusual business rules and non-obvious transformations.
• Test refresh behavior with realistic data volumes.
• Review credentials, gateway configuration, and source availability when scheduled refreshes fail.
• Keep the data model focused on the columns and rows required for reporting.

15. Practice Exercises

Exercise 1: Query Dependencies

Create one staging query and two reference queries. Open Query Dependencies and describe which queries depend on the staging query.

Exercise 2: Error Handling

Create a numeric column from mixed values such as "100", "ABC", and "250". Use a try expression to identify invalid conversions without deleting the original values.

Exercise 3: Query Folding

Connect to a supported relational database, apply a filter and column selection, and investigate whether the transformations are folded to the source.

Exercise 4: Performance Comparison

Compare two equivalent queries, one with unnecessary columns retained and one with only required columns selected. Record refresh times and inspect source activity where possible.

Exercise 5: Troubleshooting

Modify a source column name or data type, refresh the query, and identify which downstream steps or reference queries are affected. Correct the problem and validate the final output.

16. Interview Questions and Answers

Q1. What are query dependencies in Power BI?

Answer: Query dependencies describe relationships between queries when one query uses the output or logic of another. They help users understand data lineage and organize complex transformation workflows.

Q2. How can you view query dependencies?

Answer: Open Power Query Editor, select the View tab, and choose Query Dependencies.

Q3. What is the difference between Reference and Duplicate?

Answer: Reference creates a query based on an existing query and maintains a dependency on it. Duplicate copies the current query definition, allowing the new query to be modified independently.

Q4. What is error handling in Power Query?

Answer: Error handling is the process of identifying and managing failed operations or invalid values using techniques such as error inspection, removal, replacement, and try expressions.

Q5. What is the difference between Keep Errors and Remove Errors?

Answer: Keep Errors isolates rows containing errors for investigation. Remove Errors discards rows containing errors in the selected column or selection.

Q6. What does the try expression do?

Answer: It evaluates an expression while capturing its success or failure in a record. It can be combined with otherwise to provide a fallback result.

Q7. What is query folding?

Answer: Query folding translates supported Power Query transformations into operations that can execute at the underlying data source.

Q8. Why is query folding important?

Answer: It can reduce data transfer and local processing, improve refresh performance, and make transformations more scalable for large datasets.

Q9. What is partial query folding?

Answer: Partial folding occurs when some transformations are executed by the source while other transformations are processed by the Power Query engine.

Q10. Does an unavailable View Native Query option prove that folding is broken?

Answer: No. The option may be unavailable because of the connector, selected step, or interface limitations. Other indicators and diagnostics may be needed.

Q11. Can query folding work with Excel files?

Answer: Traditional SQL-style folding is generally associated with sources such as relational databases. Local Excel and CSV sources usually do not support the same source-side query folding behavior.

Q12. How can query dependencies help troubleshoot errors?

Answer: They help trace a failed output query back to the upstream query or transformation that supplies its data.

Q13. What is the risk of replacing every error with zero?

Answer: It may hide invalid records and distort totals, averages, and other business metrics. Replacement should follow a justified business rule.

Q14. How can you improve Power Query refresh performance?

Answer: Remove unnecessary columns, apply selective filters, investigate folding, optimize source queries, avoid unnecessary transformations, and use diagnostics to locate bottlenecks.

Q15. Do reference queries guarantee that the data source is read only once?

Answer: No. Query evaluation, caching, refresh behavior, and connector implementation determine how often the source is accessed.

17. Quick Revision Checklist

• Understand the difference between a query and an Applied Step.
• Use Query Dependencies to inspect relationships between queries.
• Know when to use Reference and when to use Duplicate.
• Identify the cause of errors before removing or replacing them.
• Use try and otherwise to handle expected conversion failures.
• Preserve invalid source values when they are needed for auditing.
• Understand full folding, partial folding, and local processing.
• Investigate folding using available native-query options, indicators, or diagnostics.
• Apply supported filters and column selections early when appropriate.
• Test refresh performance and validate report totals after transformations.

18. Conclusion

Query Dependencies, Error Handling, and Query Folding are foundational concepts for building dependable Power BI data preparation workflows. Query dependencies help explain how data moves between queries, error handling helps preserve data quality and control transformation failures, and query folding can improve performance by delegating supported operations to the source.

When these techniques are used together, Power BI developers can create transformation pipelines that are easier to maintain, simpler to troubleshoot, and better suited to larger datasets. The most effective approach is to design a clear query structure, handle invalid values according to business requirements, and verify performance with actual source behavior rather than relying on assumptions.

Explore Microsoft Power BI Training: View the Power BI Course

Register for a Demo: Book a Course Demo

Visit JustAcademy: www.justacademy.co